/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;

import ModelLayer.Invoice;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Henrik-iMac
 */
public class DBInvoice implements IFDBInvoice{
    private Connection con;

    public DBInvoice(){
        con = DbConnection.getInstance().getDBcon();
    }

    //find invoice by id
    public Invoice findInvoiceById(int invoiceNo){
        String wClause = "invoiceNo = " + invoiceNo;
        System.out.println("Search invoice: " + wClause);
        return singleWhere(wClause);
    }

    //insert a new invoice
    public int insertInvoice(Invoice invoice){

        int nextInvoice = GetMax.getMaxId("Select max(invoiceNo) from Invoice");
        nextInvoice = nextInvoice + 1;
        System.out.println("Next invoice# = "+nextInvoice);

        int rc = -1;
        String query ="INSERT INTO Invoice(invoiceNo, paymentDate, amount) VALUES("+
                nextInvoice + ", '"+
                invoice.getPaymentDate() +"', "+
                invoice.getAmount() + ")";
        System.out.println("insert: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Insert exception in invoice db: "+e);
        }
        return(rc);
    }
    //update information about a invoice
    public int updateInvoice(Invoice invoice){
        Invoice iObj = invoice;
        int rc = -1;
        
        String query ="UPDATE Invoice SET " +
                "invoiceNo = " + iObj.getInvoiceNo() + ", "+
                "paymentDate = '" + iObj.getPaymentDate() + "', "+
                "amount = " + iObj.getAmount() + ", "+
                "WHERE invoiceNo = " + iObj.getInvoiceNo();
        System.out.println("Update query: " +query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);

            stmt.close();
        } 
        catch (Exception e) {
            System.out.println("Update exception in Invoice db: "+e);
        }
        return rc;
    }

    private ArrayList<Invoice> miscWhere(String wClause){
        ResultSet results;
        ArrayList<Invoice> list = new ArrayList<Invoice>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            int snr = 0;

            while(results.next()){
                Invoice iObj = new Invoice();
                iObj = buildInvoice(results);
                list.add(iObj);
            }
            stmt.close();
        } 
        catch (Exception e) {
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return list;
    }

    //Singlewhere is used when we only select one supplier
    private Invoice singleWhere(String wClause)
    {
        ResultSet results;
        Invoice iObj = new Invoice();
        iObj.setInvoiceNo(0);
        String query = buildQuery(wClause);
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if(results.next()){
                iObj = buildInvoice(results);
                stmt.close();
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: "+e);
        }
        return iObj;
    }

    //method to build the query
    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM Invoice";
        if (wClause.length()>0){
            query=query+" WHERE "+ wClause;
        }
        return query;
    }

    private Invoice buildInvoice(ResultSet results){
        Invoice iObj = new Invoice();
        try {
            iObj.setInvoiceNo(results.getInt(1));
            iObj.setPaymentDate(results.getString(2));
            iObj.setAmount(results.getDouble(3));
        }
        catch (Exception e) {
            System.out.println("error in building the invoice object");
        }
        return iObj;
    }
}
